iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 3
0
自我挑戰組

MySQL 學習筆記系列 第 3 篇

MySQL 基本指令運用

11th鐵人賽 mysql
2471 瀏覽

  • 分享至 

  • xImage
    •  

前言

  • 下列的操作真的很基本，基本到不太能再往下挖了。
  • 反正…太入門的書，讀者覺得太簡單，心裡會有「這有需要實際打一遍嗎？」的想法。
  • 技術過硬的內容，也會嚇到剛接觸終端機操作的朋友，心裡會有「不然先不要好了」的想法。
  • 不如透過一些簡易、重複的操作，來熟悉 database 的特性。
  • 做筆記之前，剛和一位資訊產業的前輩討論完，言談之間，發現 database 的操作就像日常生活中的空氣、水一樣，很重要但感覺不到它的存在。學界花一學期可能只教了增刪改查，同時業界短時間內可能要完成一對一、一對多、多對多、正規化等等聽起來抽像但基本的流程。
  • 掌握簡單的步驟，對接下來觀念的釐清有很大的幫助。

練習

上一篇有基本指令的介紹，接下來看似重複的操作，有助於進一步瞭解庫與表的關系以及除錯的錯行。

建一個資料庫 brad

MariaDB [(none)]> create database brad;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| brad               |
| class              |
| cy                 |
| db1                |
| iii                |
| information_schema |
| mysql              |
| northwind          |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
 

MariaDB [(none)]> use brad;
Database changed
MariaDB [brad]> select database();
+------------+
| database() |
+------------+
| brad       |
+------------+

查看目前位置

MariaDB [brad]> select database();
+------------+
| database() |
+------------+
| brad       |
+------------+

在指定的資料庫裏面新增一個資料表 cust ，並留意附加資訊變動了哪些部份

MariaDB [brad]> create table cust(id int, cname varchar(100), tel varchar(20) , birthday date);
Query OK, 0 rows affected (0.107 sec)

MariaDB [brad]> show tables;
+----------------+
| Tables_in_brad |
+----------------+
| cust           |
+----------------+
1 row in set (0.001 sec)

查看表格詳細內容

MariaDB [brad]> desc cust;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| cname    | varchar(100) | YES  |     | NULL    |       |
| tel      | varchar(20)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

查看欄位內有沒有資料

MariaDB [brad]> select * from cust;
Empty set (0.000 sec)

在資料表格cust內寫入資料

MariaDB [brad]> insert into  cust value (1, 'brad' ,'123' , '1999-01-02');
Query OK, 1 row affected (0.050 sec)

再查詢一次資料

MariaDB [brad]> select * from cust;
+------+-------+------+------------+
| id   | cname | tel  | birthday   |
+------+-------+------+------------+
|    1 | brad  | 123  | 1999-01-02 |
+------+-------+------+------------+
1 row in set (0.000 sec)

繼續輸入第二筆資料

MariaDB [brad]> insert into cust value (3, 'john' , '456','2122-09-09');
Query OK, 1 row affected (0.050 sec)

MariaDB [brad]> select * from cust;
+------+-------+------+------------+
| id   | cname | tel  | birthday   |
+------+-------+------+------------+
|    1 | brad  | 123  | 1999-01-02 |
|    2 | tony  | 321  | 2001-09-09 |
|    3 | john  | 456  | 2122-09-09 |
+------+-------+------+------------+
3 rows in set (0.000 sec)

在整數欄位(int類型欄位)丟入字串或英文 id欄位會顯示"0"

MariaDB [brad]> insert into cust value ('cy', 'john' , '456','2122-09-09');
Query OK, 1 row affected, 1 warning (0.050 sec)

MariaDB [brad]> select * from cust;
+------+-------+------+------------+
| id   | cname | tel  | birthday   |
+------+-------+------+------------+
|    1 | brad  | 123  | 1999-01-02 |
|    2 | tony  | 321  | 2001-09-09 |
|    3 | john  | 456  | 2122-09-09 |
|    0 | john  | 456  | 2122-09-09 |
+------+-------+------+------------+

MariaDB [brad]> insert into cust value (9487945123, 'john' , '456','2122-09-09');
Query OK, 1 row affected, 1 warning (0.050 sec)
//因為id輸入的數值已經超過欄位最高數值21億....故只顯示最高數值21億....
MariaDB [brad]> select * from cust;
+------------+-------+------+------------+
| id         | cname | tel  | birthday   |
+------------+-------+------+------------+
|          1 | brad  | 123  | 1999-01-02 |
|          2 | tony  | 321  | 2001-09-09 |
|          3 | john  | 456  | 2122-09-09 |
|          0 | john  | 456  | 2122-09-09 |
| 2147483647 | john  | 456  | 2122-09-09 |
+------------+-------+------+------------+
5 rows in set (0.000 sec)

利用指定欄位寫入資料

MariaDB [brad]> insert into cust(cname, tel ,birthday) value ('tony' ,'666', '2003-09-08');
Query OK, 1 row affected (0.051 sec)

MariaDB [brad]> show tables;
+----------------+
| Tables_in_brad |
+----------------+
| cust           |
+----------------+

MariaDB [brad]> select * from cust;
+------------+-------+------+------------+
| id         | cname | tel  | birthday   |
+------------+-------+------+------------+
|          1 | brad  | 123  | 1999-01-02 |
|          2 | tony  | 321  | 2001-09-09 |
|          3 | john  | 456  | 2122-09-09 |
|          0 | john  | 456  | 2122-09-09 |
| 2147483647 | john  | 456  | 2122-09-09 |
|       NULL | tony  | 666  | 2003-09-08 |
+------------+-------+------+------------+

MariaDB [brad]> desc cust;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| cname    | varchar(100) | YES  |     | NULL    |       |
| tel      | varchar(20)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

沒有給id 所以進入到資料表內id會顯示NULL

MariaDB [brad]> insert into cust(cname, tel ,birthday) value ('tony' ,'122334444444', '2003-09-08');
Query OK, 1 row affected (0.049 sec)

MariaDB [brad]> show tables;
+----------------+
| Tables_in_brad |
+----------------+
| cust           |
+----------------+
 
MariaDB [brad]> select * from cust;
+------------+-------+--------------+------------+
| id         | cname | tel          | birthday   |
+------------+-------+--------------+------------+
|          1 | brad  | 123          | 1999-01-02 |
|          2 | tony  | 321          | 2001-09-09 |
|          3 | john  | 456          | 2122-09-09 |
|          0 | john  | 456          | 2122-09-09 |
| 2147483647 | john  | 456          | 2122-09-09 |
|       NULL | tony  | 666          | 2003-09-08 |
|       NULL | tony  | 122334444444 | 2003-09-08 |
+------------+-------+--------------+------------+

tel欄位設定長度是20 所以若輸入超過會被截掉

MariaDB [brad]> insert into cust(cname, tel ,birthday) value ('tony' ,'12345678901234567890112', '2003-09-08');
Query OK, 1 row affected, 1 warning (0.050 sec)

MariaDB [brad]> select * from cust;        
+------------+-------+----------------------+------------+
| id         | cname | tel                  | birthday   |
+------------+-------+----------------------+------------+
|          1 | brad  | 123                  | 1999-01-02 |
|          2 | tony  | 321                  | 2001-09-09 |
|          3 | john  | 456                  | 2122-09-09 |
|          0 | john  | 456                  | 2122-09-09 |
| 2147483647 | john  | 456                  | 2122-09-09 |
|       NULL | tony  | 666                  | 2003-09-08 |
|       NULL | tony  | 122334444444         | 2003-09-08 |
|       NULL | tony  | 12345678901234567890 | 2003-09-08 |
+------------+-------+----------------------+------------+
8 rows in set (0.000 sec)

MariaDB [brad]> insert into cust(cname, tel ,birthday) value ('tony' ,
    -> '今天天氣很好，全班都濕透了，因為沒有冷氣的關係'
    -> ,'1999-01-01');             
Query OK, 1 row affected, 1 warning (0.051 sec)

MariaDB [brad]> select * from cust;
+------------+-------+-------------------------------------------+------------+
| id         | cname | tel                 		         | birthday   |
+------------+-------+-------------------------------------------+------------+
|          1 | brad  | 123             		                 | 1999-01-02 |
|          2 | tony  | 321              		         | 2001-09-09 |
|          3 | john  | 456               		         | 2122-09-09 |
|          0 | john  | 456              		         | 2122-09-09 |
| 2147483647 | john  | 456               		         | 2122-09-09 |
|       NULL | tony  | 666              		         | 2003-09-08 |
|       NULL | tony  | 122334444444       		         | 2003-09-08 |
|       NULL | tony  | 12345678901234567890                      | 2003-09-08 |
|       NULL | tony  | 今天天氣很好，全班都濕透了，因為沒有冷氣  | 1999-01-01 |
+------------+-------+-------------------------------------------+------------+
9 rows in set (0.000 sec)

在birthady欄位輸入不符的日期會顯示0000-00-00

MariaDB [brad]> insert into cust (birthday) value ('1999-00-34');
Query OK, 1 row affected, 1 warning (0.050 sec)

MariaDB [brad]> select * from cust ;
+------------+-------+----------------------+------------+
| id         | cname | tel                  | birthday   |
+------------+-------+----------------------+------------+
|          1 | brad  | 123                  | 1999-01-02 |
|          2 | tony  | 321                  | 2001-09-09 |
|          3 | john  | 456                  | 2122-09-09 |
|          0 | john  | 456                  | 2122-09-09 |
| 2147483647 | john  | 456                  | 2122-09-09 |
|       NULL | tony  | 666                  | 2003-09-08 |
|       NULL | tony  | 122334444444         | 2003-09-08 |
|       NULL | tony  | 12345678901234567890 | 2003-09-08 |
|       NULL | tony  | ???????????????????? | 1999-01-01 |
|       NULL | NULL  | NULL                 | 0000-00-00 |
+------------+-------+----------------------+------------+
10 rows in set (0.001 sec)

生日欄位加上時分秒，可以寫入 但不會顯示

MariaDB [brad]> insert into cust (birthday) value ('1999-01-31 10:10:10');
Query OK, 1 row affected, 1 warning (0.050 sec)

MariaDB [brad]> select *from cust;
+------------+-------+----------------------+------------+
| id         | cname | tel                  | birthday   |
+------------+-------+----------------------+------------+
|          1 | brad  | 123                  | 1999-01-02 |
|          2 | tony  | 321                  | 2001-09-09 |
|          3 | john  | 456                  | 2122-09-09 |
|          0 | john  | 456                  | 2122-09-09 |
| 2147483647 | john  | 456                  | 2122-09-09 |
|       NULL | tony  | 666                  | 2003-09-08 |
|       NULL | tony  | 122334444444         | 2003-09-08 |
|       NULL | tony  | 12345678901234567890 | 2003-09-08 |
|       NULL | tony  | ???????????????????? | 1999-01-01 |
|       NULL | NULL  | NULL                 | 0000-00-00 |
|       NULL | NULL  | NULL                 | 1999-01-31 |
+------------+-------+----------------------+------------+
11 rows in set (0.000 sec)

生日欄位加上時分秒，可以寫入 但若輸入錯誤的時分 秒，則會整串資料變成0000-00-00

MariaDB [brad]> insert into cust (birthday) value ('1999-01-31 10:10:80');
Query OK, 1 row affected, 1 warning (0.050 sec)

MariaDB [brad]> select * from cust;
+------------+-------+----------------------+------------+
| id         | cname | tel                  | birthday   |
+------------+-------+----------------------+------------+
|          1 | brad  | 123                  | 1999-01-02 |
|          2 | tony  | 321                  | 2001-09-09 |
|          3 | john  | 456                  | 2122-09-09 |
|          0 | john  | 456                  | 2122-09-09 |
| 2147483647 | john  | 456                  | 2122-09-09 |
|       NULL | tony  | 666                  | 2003-09-08 |
|       NULL | tony  | 122334444444         | 2003-09-08 |
|       NULL | tony  | 12345678901234567890 | 2003-09-08 |
|       NULL | tony  | ???????????????????? | 1999-01-01 |
|       NULL | NULL  | NULL                 | 0000-00-00 |
|       NULL | NULL  | NULL                 | 1999-01-31 |
|       NULL | NULL  | NULL                 | 0000-00-00 |
+------------+-------+----------------------+------------+
12 rows in set (0.000 sec)

寫入欄位值給id數字,會照順序排列，若沒有給id數字，則會以遞增方式加入

MariaDB [iii]> insert into tb1 (id, f1) values (5,'APPLE');
Query OK, 1 row affected (0.050 sec)

MariaDB [iii]> select * from tb1;                    
+-----+-------+
| id  | f1    |
+-----+-------+
|   1 | CY    |
|   2 | Brad  |
|   5 | APPLE |
|  99 | 1111  |
| 100 | qqq   |
+-----+-------+

MariaDB [iii]> insert into tb1 (f1) values ('qqq'); 
//沒給指定數字會遞增加入
Query OK, 1 row affected (0.049 sec)

MariaDB [iii]> select * from tb1;
+-----+-------+
| id  | f1    |
+-----+-------+
|   1 | CY    |
|   2 | Brad  |
|   5 | APPLE |
|  99 | 1111  |
| 100 | qqq   |
| 101 | qqq   |
+-----+-------+

重新修改欄位長度，原本的字串會被截斷

MariaDB [iii]> select * from tb1;
+-----+-------+
| id  | f1    |
+-----+-------+
|   1 | CY    |
|   2 | Brad  |
|   5 | APPLE |
|  99 | 1111  |
| 100 | qq11  |
| 101 | qq11  |
| 102 | apple |
+-----+-------+

MariaDB [iii]> alter table tb1 modify f1 varchar(2);//重設長度為2
Query OK, 7 rows affected, 1 warning (0.190 sec)
Records: 7  Duplicates: 0  Warnings: 1

MariaDB [iii]> select * from tb1;
//原本超過的長度字元被截斷了
+-----+------+
| id  | f1   |
+-----+------+
|   1 | CY   |
|   2 | Br   |
|   5 | AP   |
|  99 | 11   |
| 100 | qq   |
| 101 | qq   |
| 102 | ap   |
+-----+------+

字串沒加引號會輸入錯誤 ''

MariaDB [iii]> insert into tb1 (f1) value (apple);
ERROR 1054 (42S22): Unknown column 'apple' in 'field list'

將varchar改成int型別

MariaDB [iii]> alter table tb1 modify f1 int;
Query OK, 10 rows affected, 6 warnings (0.120 sec)
Records: 10  Duplicates: 0  Warnings: 6

MariaDB [iii]> select * from tb1;
//原本字串部分都變成0
+-----+------+
| id  | f1   |
+-----+------+
|   1 |    0 |
|   2 |    0 |
|   5 |    0 |
|  99 |   11 |
| 100 |    0 |
| 101 |    0 |
| 102 |    0 |
| 103 |   11 |
| 104 |   12 |
| 105 |   -1 |
+-----+------+

創造一個新欄位f2

MariaDB [iii]> alter table tb1 add f2 varchar(12);
Query OK, 0 rows affected (0.019 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [iii]> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f1    | int(11)     | YES  |     | NULL    |                |
| f2    | varchar(12) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

創建新欄位並指定插入位置

MariaDB [iii]> alter table tb1 add f3 varchar(10) after f1;
Query OK, 0 rows affected (0.109 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [iii]> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f1    | int(11)     | YES  |     | NULL    |                |
| f3    | varchar(10) | YES  |     | NULL    |                |
| f2    | varchar(12) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

MariaDB [iii]> alter table tb1 add f4 varchar(10)  first;
//最後first 則會顯示在第一個欄位
Query OK, 0 rows affected (0.062 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [iii]> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| f4    | varchar(10) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f1    | int(11)     | YES  |     | NULL    |                |
| f3    | varchar(10) | YES  |     | NULL    |                |
| f2    | varchar(12) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

變更欄位名稱

MariaDB [iii]> alter table tb1 change f1 newf1 varchar(11);
Query OK, 10 rows affected (0.069 sec)
Records: 10  Duplicates: 0  Warnings: 0

MariaDB [iii]> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| f4    | varchar(10) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| newf1 | varchar(11) | YES  |     | NULL    |                |
| f3    | varchar(10) | YES  |     | NULL    |                |
| f2    | varchar(12) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

將表格中一個欄位刪掉 drop

MariaDB [iii]> alter table tb1 drop f2;//將f2欄位刪除
Query OK, 0 rows affected (0.107 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [iii]> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| f4    | varchar(10) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| newf1 | varchar(11) | YES  |     | NULL    |                |
| f3    | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

進行資料修改 update

MariaDB [iii]> update tb1 set f4 ='brad';
Query OK, 10 rows affected (0.002 sec)
Rows matched: 10  Changed: 10  Warnings: 0

MariaDB [iii]> select *from tb1;//因為沒有設條件  所以都會顯示brad
+------+-----+-------+------+
| f4   | id  | newf1 | f3   |
+------+-----+-------+------+
| brad |   1 | 0     | NULL |
| brad |   2 | 0     | NULL |
| brad |   5 | 0     | NULL |
| brad |  99 | 11    | NULL |
| brad | 100 | 0     | NULL |
| brad | 101 | 0     | NULL |
| brad | 102 | 0     | NULL |
| brad | 103 | 11    | NULL |
| brad | 104 | 12    | NULL |
| brad | 105 | -1    | NULL |
+------+-----+-------+------+

加入條件式where後來修改

MariaDB [iii]> update tb1 set f4 ='Eric' where id =103;
Query OK, 1 row affected (0.012 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [iii]> select *from tb1; 
+------+-----+-------+------+
| f4   | id  | newf1 | f3   |
+------+-----+-------+------+
| brad |   1 | 0     | NULL |
| brad |   2 | 0     | NULL |
| brad |   5 | 0     | NULL |
| brad |  99 | 11    | NULL |
| brad | 100 | 0     | NULL |
| brad | 101 | 0     | NULL |
| brad | 102 | 0     | NULL |
| Eric | 103 | 11    | NULL |
| brad | 104 | 12    | NULL |
| brad | 105 | -1    | NULL |
+------+-----+-------+------+

將id大於100都做修改

MariaDB [iii]> update tb1 set f4 ='brad' ,f3='test' where id>=100;
Query OK, 6 rows affected (0.013 sec)
Rows matched: 6  Changed: 6  Warnings: 0

MariaDB [iii]> select *from tb1;
+------+-----+-------+------+
| f4   | id  | newf1 | f3   |
+------+-----+-------+------+
| brad |   1 | 0     | NULL |
| brad |   2 | 0     | NULL |
| brad |   5 | 0     | NULL |
| brad |  99 | 11    | NULL |
| brad | 100 | 0     | test |
| brad | 101 | 0     | test |
| brad | 102 | 0     | test |
| brad | 103 | 11    | test |
| brad | 104 | 12    | test |
| brad | 105 | -1    | test |
+------+-----+-------+------+

砍掉一筆資料 delete

MariaDB [iii]> delete from tb1 where id = 103;
Query OK, 1 row affected (0.014 sec)

MariaDB [iii]> select *from tb1;
+------+-----+-------+------+
| f4   | id  | newf1 | f3   |
+------+-----+-------+------+
| brad |   1 | 0     | NULL |
| brad |   2 | 0     | NULL |
| brad |   5 | 0     | NULL |
| brad |  99 | 11    | NULL |
| brad | 100 | 0     | test |
| brad | 101 | 0     | test |
| brad | 102 | 0     | test |
| brad | 104 | 12    | test |
| brad | 105 | -1    | test |
+------+-----+-------+------+

後記

  • 上列的內容真的很枯燥
  • 1天一小步，21天後一大步
  • 這些指令是由一位 mentor 在極短的時間內，一邊講解一邊 live coding 出來的。
  • 單看指令和結果真的很有催眠的效果。
  • 同時也反省如何練就迅速一發一修正的能力。

So far so good! 庫與表的遠征之路還沒正式開始呢！
to be continued~


上一篇
MySQL 基本操作
下一篇
10人小班的基本資料庫操作
系列文
MySQL 學習筆記 共 8 篇
  1. 4
    10人小班的基本資料庫操作
  2. 5
    資料庫延伸操作
  3. 6
    運用 where... in...和 join
  4. 7
    create view
  5. 8
    北風資料庫情境模擬
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22195 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙